

package com.gree.shyun.server.db;


import com.gree.shyun.server.db.ex.DbException;
import com.gree.shyun.server.db.sqlite.*;
import com.gree.shyun.server.db.table.DbModel;
import com.gree.shyun.server.db.table.Id;
import com.gree.shyun.server.db.table.Table;
import com.gree.shyun.server.db.table.TableUtils;
import com.gree.shyun.server.db.util.LogUtils;
import com.gree.shyun.server.db.util.TextUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

public class DbManager {

    //*************************************** create instance ****************************************************

    /**
     * key: dbName
     */
    private static HashMap<String, DbManager> daoMap = new HashMap<String, DbManager>();

    private DataBase database;
    private boolean debug = false;
    private String dbName;
    private boolean allowTransaction = false;
    private static DbManager db ;

    /**
     *  初始化
     * @return
     */
    public synchronized static DbManager getInstance(String dbName) {
        if(db==null){
            synchronized (DbManager.class){
                db = new DbManager();
                try {
                    db.database = DataBase.getInstance(dbName);
                    db.dbName = dbName;
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return db;
    }


    public String getDbName(){
        return dbName;
    }

    public DbManager configDebug(boolean debug) {
        this.debug = debug;
        return this;
    }

    public DbManager configAllowTransaction(boolean allowTransaction) {
        this.allowTransaction = allowTransaction;
        return this;
    }

    public DataBase getDatabase() {
        return database;
    }


    //*********************************************** operations ********************************************************

    public void saveOrUpdate(Object entity,String... updateColumnNames) throws DbException {
        try {
            beginTransaction();

            createTableIfNotExist(entity.getClass());
            saveOrUpdateWithoutTransaction(entity,updateColumnNames);

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void saveOrUpdateAll(List<?> entities,String... updateColumnNames) throws DbException {
        if (entities == null || entities.size() == 0) return;
        try {
            beginTransaction();

            createTableIfNotExist(entities.get(0).getClass());
            for (Object entity : entities) {
                saveOrUpdateWithoutTransaction(entity,updateColumnNames);
            }

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void replace(Object entity) throws DbException {
        try {
            beginTransaction();

            createTableIfNotExist(entity.getClass());
            execNonQuery(SqlInfoBuilder.buildReplaceSqlInfo(this, entity));

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void replaceAll(List<?> entities) throws DbException {
        if (entities == null || entities.size() == 0) return;
        try {
            beginTransaction();

            createTableIfNotExist(entities.get(0).getClass());
            for (Object entity : entities) {
                execNonQuery(SqlInfoBuilder.buildReplaceSqlInfo(this, entity));
            }

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void save(Object entity) throws DbException {
        try {
            beginTransaction();

            createTableIfNotExist(entity.getClass());
            execNonQuery(SqlInfoBuilder.buildInsertSqlInfo(this, entity));

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void saveAll(List<?> entities) throws DbException {
        if (entities == null || entities.size() == 0) return;
        try {
            beginTransaction();

            createTableIfNotExist(entities.get(0).getClass());
            for (Object entity : entities) {
                execNonQuery(SqlInfoBuilder.buildInsertSqlInfo(this, entity));
            }

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public boolean saveBindingId(Object entity) throws DbException {
        boolean result = false;
        try {
            beginTransaction();

            createTableIfNotExist(entity.getClass());
            result = saveBindingIdWithoutTransaction(entity);

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
        return result;
    }

    public void saveBindingIdAll(List<?> entities) throws DbException {
        if (entities == null || entities.size() == 0) return;
        try {
            beginTransaction();

            createTableIfNotExist(entities.get(0).getClass());
            for (Object entity : entities) {
                if (!saveBindingIdWithoutTransaction(entity)) {
                    throw new DbException("saveBindingId error, transaction will not commit!");
                }
            }

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void deleteById(Class<?> entityType, Object idValue) throws DbException {
        if (!tableIsExist(entityType)) return;
        try {
            beginTransaction();

            execNonQuery(SqlInfoBuilder.buildDeleteSqlInfo(this, entityType, idValue));

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void delete(Object entity) throws DbException {
        if (!tableIsExist(entity.getClass())) return;
        try {
            beginTransaction();
            execNonQuery(SqlInfoBuilder.buildDeleteSqlInfo(this, entity));
            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void delete(Class<?> entityType, WhereBuilder whereBuilder) throws DbException {
        if (!tableIsExist(entityType)) return;
        try {
            beginTransaction();
            execNonQuery(SqlInfoBuilder.buildDeleteSqlInfo(this, entityType, whereBuilder));
            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void deleteAll(List<?> entities) throws DbException {
        if (entities == null || entities.size() == 0 || !tableIsExist(entities.get(0).getClass())) return;
        try {
            beginTransaction();

            for (Object entity : entities) {
                execNonQuery(SqlInfoBuilder.buildDeleteSqlInfo(this, entity));
            }

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void deleteAll(Class<?> entityType) throws DbException {
        delete(entityType, null);
    }

    public void update(Object entity, String... updateColumnNames) throws DbException {
        if (!tableIsExist(entity.getClass())) return;
        try {
            beginTransaction();

            execNonQuery(SqlInfoBuilder.buildUpdateSqlInfo(this, entity, updateColumnNames));

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void update(Object entity, WhereBuilder whereBuilder, String... updateColumnNames) throws DbException {
        if (!tableIsExist(entity.getClass())) return;
        try {
            beginTransaction();

            execNonQuery(SqlInfoBuilder.buildUpdateSqlInfo(this, entity, whereBuilder, updateColumnNames));

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void updateAll(List<?> entities, String... updateColumnNames) throws DbException {
        if (entities == null || entities.size() == 0 || !tableIsExist(entities.get(0).getClass())) return;
        try {
            beginTransaction();

            for (Object entity : entities) {
                execNonQuery(SqlInfoBuilder.buildUpdateSqlInfo(this, entity, updateColumnNames));
            }

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    public void updateAll(List<?> entities, WhereBuilder whereBuilder, String... updateColumnNames) throws DbException {
        if (entities == null || entities.size() == 0 || !tableIsExist(entities.get(0).getClass())) return;
        try {
            beginTransaction();

            for (Object entity : entities) {
                execNonQuery(SqlInfoBuilder.buildUpdateSqlInfo(this, entity, whereBuilder, updateColumnNames));
            }

            setTransactionSuccessful();
        } finally {
            endTransaction();
        }
    }

    @SuppressWarnings("unchecked")
    public <T> T findById(Class<T> entityType, Object idValue) throws DbException {
        if (!tableIsExist(entityType)) return null;

        Table table = Table.get(this, entityType);
        Selector selector = Selector.from(entityType).where(table.id.getColumnName(), "=", idValue);

        String sql = selector.limit(1).toString();
        long seq = ResultSetUtils.FindCacheSequence.getSeq();
        findTempCache.setSeq(seq);
        Object obj = findTempCache.get(sql);
        if (obj != null) {
            return (T) obj;
        }

        ResultSet resultSet = execQuery(sql);
        if (resultSet != null) {
            try {
                if (resultSet.next()) {
                    T entity = ResultSetUtils.getEntity(this, resultSet, entityType, seq);
                    findTempCache.put(sql, entity);
                    return entity;
                }
            } catch (Throwable e) {
                throw new DbException(e);
            } finally {
                endTransaction();
            }
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    public <T> T findFirst(Selector selector) throws DbException {
        if (!tableIsExist(selector.getEntityType())) return null;

        String sql = selector.limit(1).toString();
        long seq = ResultSetUtils.FindCacheSequence.getSeq();
        findTempCache.setSeq(seq);
        Object obj = findTempCache.get(sql);
        if (obj != null) {
            return (T) obj;
        }

        ResultSet resultSet = execQuery(sql);
        if (resultSet != null) {
            try {
                if (resultSet.next()) {
                    T entity = (T) ResultSetUtils.getEntity(this, resultSet, selector.getEntityType(), seq);
                    findTempCache.put(sql, entity);
                    return entity;
                }
            } catch (Throwable e) {
                throw new DbException(e);
            } finally {
                endTransaction();
            }
        }
        return null;
    }

    public <T> T findFirst(Class<T> entityType) throws DbException {
        return findFirst(Selector.from(entityType));
    }

    @SuppressWarnings("unchecked")
    public <T> List<T> findAll(Selector selector) throws DbException {
        if (!tableIsExist(selector.getEntityType())) return null;

        String sql = selector.toString();
        long seq = ResultSetUtils.FindCacheSequence.getSeq();
        findTempCache.setSeq(seq);
        Object obj = findTempCache.get(sql);
        if (obj != null) {
            return (List<T>) obj;
        }

        List<T> result = new ArrayList<T>();

        ResultSet resultSet = execQuery(sql);
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    T entity = (T) ResultSetUtils.getEntity(this, resultSet, selector.getEntityType(), seq);
                    result.add(entity);
                }
                findTempCache.put(sql, result);
            } catch (Throwable e) {
                throw new DbException(e);
            } finally {
                endTransaction();
            }
        }
        return result;
    }

    public <T> List<T> findAll(Class<T> entityType) throws DbException {
        return findAll(Selector.from(entityType));
    }

    public DbModel findDbModelFirst(SqlInfo sqlInfo) throws DbException {
        ResultSet resultSet = execQuery(sqlInfo);
        if (resultSet != null) {
            try {
                if (resultSet.next()) {
                    return ResultSetUtils.getDbModel(resultSet);
                }
            } catch (Throwable e) {
                throw new DbException(e);
            } finally {
                endTransaction();
            }
        }
        return null;
    }

    public DbModel findDbModelFirst(DbModelSelector selector) throws DbException {
        if (!tableIsExist(selector.getEntityType())) return null;
        SqlInfo sqlInfo = new SqlInfo();
        sqlInfo.setSql(selector.limit(1).toString());
        return  findDbModelFirst(sqlInfo);
    }

    public List<DbModel> findDbModelAll(SqlInfo sqlInfo) throws DbException {
        List<DbModel> dbModelList = new ArrayList<DbModel>();
        ResultSet resultSet = execQuery(sqlInfo);
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    dbModelList.add(ResultSetUtils.getDbModel(resultSet));
                }
            } catch (Throwable e) {
                throw new DbException(e);
            } finally {
                endTransaction();
            }
        }
        return dbModelList;
    }

    public List<DbModel> findDbModelAll(DbModelSelector selector) throws DbException {
        if (!tableIsExist(selector.getEntityType())) return null;
        SqlInfo sqlInfo = new SqlInfo();
        sqlInfo.setSql(selector.toString());
        return  findDbModelAll(sqlInfo);
    }

    public long count(Selector selector) throws DbException {
        Class<?> entityType = selector.getEntityType();
        if (!tableIsExist(entityType)) return 0;

        Table table = Table.get(this, entityType);
        DbModelSelector dmSelector = selector.select("count(" + table.id.getColumnName() + ") as count");
        return findDbModelFirst(dmSelector).getLong("count");
    }

    public long count(Class<?> entityType) throws DbException {
        return count(Selector.from(entityType));
    }

    //******************************************** config ******************************************************



    //***************************** private operations with out transaction *****************************
    private void saveOrUpdateWithoutTransaction(Object entity,String... updateColumnNames) throws DbException {
        Table table = Table.get(this, entity.getClass());
        Id id = table.id;
        if (id.isAutoIncrement()) {
            Object idValue = id.getColumnValue(entity);
            if (idValue!= null) {
                Object obj =  findById(entity.getClass(),idValue);
                if(obj==null){
                    saveBindingIdWithoutTransaction(entity);
                }else {
                    execNonQuery(SqlInfoBuilder.buildUpdateSqlInfo(this, entity, updateColumnNames));
                }
            } else {
                saveBindingIdWithoutTransaction(entity);
            }
        } else {
            Object idValue = id.getColumnValue(entity);
            Object obj =  findById(entity.getClass(),idValue);
            if(obj==null){
                execNonQuery(SqlInfoBuilder.buildInsertSqlInfo(this, entity));
            }else {
                execNonQuery(SqlInfoBuilder.buildUpdateSqlInfo(this, entity, updateColumnNames));
            }
        }
    }

    private boolean saveBindingIdWithoutTransaction(Object entity) throws DbException {
        Class<?> entityType = entity.getClass();
        Table table = Table.get(this, entityType);
        Id idColumn = table.id;
        if (idColumn.isAutoIncrement()) {
            execNonQuery(SqlInfoBuilder.buildInsertSqlInfo(this, entity));
            long id = getLastAutoIncrementId(table.tableName);
            if (id == -1) {
                return false;
            }
            idColumn.setAutoIncrementId(entity, id);
            return true;
        } else {
            execNonQuery(SqlInfoBuilder.buildInsertSqlInfo(this, entity));
            return true;
        }
    }

    //************************************************ tools ***********************************

    private long getLastAutoIncrementId(String tableName) throws DbException {
        long id = -1;
        ResultSet resultSet = execQuery("SELECT seq FROM sqlite_sequence WHERE name='" + tableName + "'");
        if (resultSet != null) {
            try {
                if (resultSet.next()) {
                    id = resultSet.getLong(1);
                }
            } catch (Throwable e) {
                throw new DbException(e);
            } finally {
                endTransaction();
            }
        }
        return id;
    }

    public void createTableIfNotExist(Class<?> entityType) throws DbException {
        if (!tableIsExist(entityType)) {
            try {
                beginTransaction();
                SqlInfo sqlInfo = SqlInfoBuilder.buildCreateTableSqlInfo(this, entityType);
                execNonQuery(sqlInfo);
                String execAfterTableCreated = TableUtils.getExecAfterTableCreated(entityType);
                if (execAfterTableCreated!=null) {
                    execNonQuery(execAfterTableCreated);
                }
                setTransactionSuccessful();
             } catch (Throwable e) {
                throw new DbException(e);
            } finally {
                endTransaction(); 
            }
        }
    }

    public boolean tableIsExist(Class<?> entityType) throws DbException {
        Table table = Table.get(this, entityType);
        if (table.isCheckedDatabase()) {
            return true;
        }

        ResultSet resultSet = execQuery("SELECT COUNT(*) AS c FROM sqlite_master WHERE type='table' AND name='" + table.tableName + "'");
        if (resultSet != null) {
            try {
                if (resultSet.next()) {
                    int count = resultSet.getInt(1);
                    if (count > 0) {
                        table.setCheckedDatabase(true);
                        return true;
                    }
                }
            } catch (Throwable e) {
                throw new DbException(e);
            } finally {
                endTransaction();
            }
        }

        return false;
    }

    public void dropDb() throws DbException {
        ResultSet resultSet = execQuery("SELECT name FROM sqlite_master WHERE type='table' AND name<>'sqlite_sequence'");
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    try {
                        String tableName = resultSet.getString(1);
                        execNonQuery("DROP TABLE " + tableName);
                        Table.remove(this, tableName);
                    } catch (Throwable e) {
                        LogUtils.e(e.getMessage(), e);
                    }
                }

            } catch (Throwable e) {
                throw new DbException(e);
            } finally {
                endTransaction();
            }
        }
    }

    public void dropTable(Class<?> entityType) throws DbException {
        if (!tableIsExist(entityType)) return;
        String tableName = TableUtils.getTableName(entityType);
        execNonQuery("DROP TABLE " + tableName);
        Table.remove(this, entityType);
    }

    public void close() {
        if (daoMap.containsKey(dbName)) {
            daoMap.remove(dbName);
        }
    }

    /**
     * 执行查询 sql
     * 返回map结果集
     * 结构
     * 行 - >列  ->列名 对应 值
     * @param selector
     * @return
     */
    public  List<Map<String,Object>> exec(Selector selector) throws DbException {
        return exec(selector.toString());
    }

    /**
     *  执行查询 sql
     * 返回map结果集
     * @param sql
     * @return
     */
    public List<Map<String,Object>> exec(String sql) throws DbException {
        if(TextUtils.isEmpty(sql)){
            return null;
        }
        List<Map<String,Object>> list = new ArrayList<>();
        ResultSet cursor = null;
        try {
            cursor = execQuery(sql);
            while (cursor.next()){
                Map<String,Object> map = new HashMap<>();
                int count =  ResultSetUtils.getColumnCount(cursor);
                for(int i = 1 ; i <= count;i++){
                    Object val = null;
                    int type = ResultSetUtils.getColumnType(cursor,i);
                    switch (type){
                        case Types.NULL:
                            break;
                        case Types.INTEGER:
                            val = cursor.getInt(i);
                            break;
                        case Types.FLOAT:
                            val = cursor.getFloat(i);
                            break;
                        case Types.NVARCHAR:
                        case Types.VARCHAR:
                            val = cursor.getString(i);
                            break;
                        case Types.BLOB:
                            val = cursor.getBlob(i);
                            break;
                        case Types.BOOLEAN:
                            val = cursor.getBoolean(i);
                            break;
                        case Types.DATE:
                            val = cursor.getDate(i);
                            break;
                    }
                    map.put(ResultSetUtils.getColumnName(cursor,i),val);
                }
                list.add(map);
            }
        }catch (DbException e){
            throw new DbException(e);
        } catch (SQLException e) {
            throw new DbException(e);
        } finally {
            endTransaction();
        }
        return list;
    }

    /**
     * 获取表名
     * @param cla
     * @return
     */
    public String getTableName(Class cla){
        String tableName = cla.getName();
        tableName = tableName.replaceAll("\\.","_");
        return tableName;
    }
    /**
     *  获取表的列名
     * @param table
     * @return
     */
    public  List<String> getTableCloumn(Class table) throws DbException {
        List<String> column = new ArrayList<>();
        String sql = "select * from " + getTableName(table);
        ResultSet cursor = null;
        try {
            cursor = execQuery(sql);
            if (cursor != null) {
                int count = ResultSetUtils.getColumnCount(cursor);
                for (int j = 1; j <= count; j++) {
                    column.add(ResultSetUtils.getColumnName(cursor,j));
                }
            }
        }catch (DbException e){
            throw new DbException(e);
        } catch (SQLException e) {
            throw new DbException(e);
        } finally {
            endTransaction();
        }
        return column;
    }

    ///////////////////////////////////// exec sql /////////////////////////////////////////////////////
    private void debugSql(String sql) {
        if (debug) {
            LogUtils.d(sql);
        }
    }

    private Lock writeLock = new ReentrantLock();
    private volatile boolean writeLocked = false;

    private void beginTransaction() {
        if (allowTransaction) {
            database.beginTransaction();
        } else {
            writeLock.lock();
            writeLocked = true;
        }
    }

    private void setTransactionSuccessful() {
        if (allowTransaction) {
            database.setTransactionSuccessful();
        }
    }

    private void endTransaction() {
        if (allowTransaction) {
            database.endTransaction();
        }
        if (writeLocked) {
            writeLock.unlock();
            writeLocked = false;
        }
    }


    public void execNonQuery(SqlInfo sqlInfo) throws DbException {
        debugSql(sqlInfo.getSql());
        try {
            if (sqlInfo.getBindArgs() != null) {
                database.execSQL(sqlInfo.getSql(), sqlInfo.getBindArgsAsArray());
            } else {
                database.execSQL(sqlInfo.getSql());
            }
        } catch (Throwable e) {
            throw new DbException(e);
        }
    }

    public void execNonQuery(String sql) throws DbException {
        debugSql(sql);
        try {
            database.execSQL(sql);
        } catch (Throwable e) {
            throw new DbException(e);
        }
    }

    public ResultSet execQuery(SqlInfo sqlInfo) throws DbException {
        debugSql(sqlInfo.getSql());
        try {
            return database.rawQuery(sqlInfo.getSql(), sqlInfo.getBindArgsAsStrArray());
        } catch (Throwable e) {
            throw new DbException(e);
        }
    }

    public ResultSet execQuery(String sql) throws DbException {
        debugSql(sql);
        try {
            return database.rawQuery(sql);
        } catch (Throwable e) {
            throw new DbException(e);
        }
    }

    /////////////////////// temp cache ////////////////////////////////////////////////////////////////
    private final FindTempCache findTempCache = new FindTempCache();

    private class FindTempCache {
        private FindTempCache() {
        }

        /**
         * key: sql;
         * value: find result
         */
        private final ConcurrentHashMap<String, Object> cache = new ConcurrentHashMap<String, Object>();

        private long seq = 0;

        public void put(String sql, Object result) {
            if (sql != null && result != null) {
                cache.put(sql, result);
            }
        }

        public Object get(String sql) {
            return cache.get(sql);
        }

        public void setSeq(long seq) {
            if (this.seq != seq) {
                cache.clear();
                this.seq = seq;
            }
        }
    }

}
